﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChinaDynastyTours.Models;
using ChinaDynastyTours.Helpers;

namespace ChinaDynastyTours.Controls
{
    class PayApplyCtrl
    {
        #region 付款申请信息查询
        
        /// <summary>
        /// 根据主键payApplyId获取付款申请单数据信息
        /// </summary>
        /// <param name="payApplyId"></param>
        /// <returns></returns>
        internal static DataTable GetPayApplyDataById(string payApplyId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //string Sql = string.Format(@"SELECT * FROM fin_PayApply where PayApplyID in(select payapplyid from fin_PayDetail where expenseid={0})",expenseId);
            string Sql = string.Format(@"SELECT * FROM fin_PayApply where PayApplyID ={0}", payApplyId);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 根据指定供应商ID获取付款申请单信息
        /// </summary>
        /// <param name="providerId"></param>
        /// <returns></returns>
        internal static DataTable GetPayApplyDataByProviderID(string providerId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql="";
            //string Sql = string.Format(@"SELECT * FROM fin_PayApply where PayApplyID in(select payapplyid from fin_PayDetail where expenseid={0})",expenseId);
            if (string.IsNullOrEmpty(providerId))
                Sql = "SELECT * FROM fin_PayApply";
            else
                Sql = string.Format(@"SELECT * FROM fin_PayApply where ProviderID ={0}", providerId);
            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 根据指定供应商ID获取支付单信息   
        /// </summary>
        /// <param name="providerId"></param>
        /// <returns></returns>
        internal static DataTable GetPayDataByProviderID(string providerId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT * FROM fin_Pay WHERE ProviderID ='{0}'", providerId);
            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 根据支付单ID获取付款详情信息   
        /// </summary>
        /// <param name="providerId"></param>
        /// <returns></returns>
        internal static DataTable GetPayDetailByPayID(string payId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT e.TourgroupID,e.TourgroupName,pd.ApplyPay,pd.PayDetailID FROM fee_Expense e,fin_PayDetail pd WHERE pd.ExpenseID=e.ExpenseID and pd.PayID ='{0}'", payId);
            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 根据支付单ID获取冲抵信息   
        /// </summary>
        /// <param name="providerId"></param>
        /// <returns></returns>
        internal static DataTable GetOffsetIncomeByPayID(string payId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT ii.TourgroupID,ii.TourgroupName,i.RMBAmount,i.Remark FROM fin_Income i,fin_IncomeItem ii WHERE i.IncomeItemID = ii.IncomeItemID AND PayID ='{0}'", payId);
            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 根据指定团ID获取付款申请单信息   
        /// </summary>
        /// <param name="providerId"></param>
        /// <returns></returns>
        internal static DataTable GetPayApplyDataByTourGroupID(string tourGroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT * FROM vw_PayApply WHERE TourgroupID ='{0}'", tourGroupId);
            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 根据“财务付款状态”获取付款申请单信息
        /// </summary>
        /// <param name="payStatusEnum"></param>
        /// <returns></returns>
        internal static DataTable GetPayApplyDataByPayStatus(PayStatusEnum payStatusEnum)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT * FROM fin_PayApply WHERE PayStatus ='{0}'", payStatusEnum);
            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        #endregion

        #region 付款申请增、删、改
        /// <summary>
        ///  创建付款申请数据记录
        /// </summary>
        /// <param name="payapplyEntity"></param>
        /// <returns>新增的付款申请单ID</returns>
        internal static string CreatePayApplyAndPayDetailData(PayApplyEntity payapplyEntity, PayDetailEntity[] paydetailEntitys)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO fin_PayApply (    [ProviderID],[ProviderName], [Contact], [Telephone], [Fax], [TotalpaymentG], [TotalpaymentL], 
                                                                       [Applicants], [Audit], [PlanPayTime], [ApplyPayTime], [PayTime], [PayStatus],[Remark],[Voucher])
                                                                       VALUES({0},'{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}');Select SCOPE_IDENTITY();",
                                                                        payapplyEntity.ProviderID,
                                                                        payapplyEntity.ProviderName,
                                                                        payapplyEntity.Contact,
                                                                        payapplyEntity.Telephone,
                                                                        payapplyEntity.Fax,
                                                                        payapplyEntity.TotalpaymentG,
                                                                        payapplyEntity.TotalpaymentL,
                                                                        payapplyEntity.Applicants,
                                                                        payapplyEntity.Audit,
                                                                        payapplyEntity.PlanPayTime,
                                                                        payapplyEntity.ApplyPayTime,
                                                                        payapplyEntity.PayTime,
                                                                        payapplyEntity.PayStatus,
                                                                        payapplyEntity.Remark,
                                                                        payapplyEntity.Voucher
                                                                        );
            string payApplyId = db.ExecuteScalar(CommandType.Text, sql).ToString();

            //生成n条Insert语句，一并执行(生成一条付款申请单对应的多条付款项信息)
            StringBuilder sqlPayDetail = new StringBuilder();
            foreach (PayDetailEntity entity in paydetailEntitys)
            {
                sqlPayDetail.AppendFormat(@" INSERT INTO fin_PayDetail([PayApplyID], [ExpenseID], [ApplyPay],[Remark],[PayStatus]) VALUES({0},{1},{2},'{3}','{4}'); ",
                                payApplyId,
                                entity.ExpenseID,
                                entity.ApplyPay,
                                entity.Remark,
                                entity.PayStatus);
            }

            db.ExecuteNonQuery(CommandType.Text, sqlPayDetail.ToString());

            return payApplyId;
        }

        /// <summary>
        /// 付款申请单状态‘财务付款状态’修改‘已付、未付’两种
        /// </summary>
        /// <param name="expenseEntity"></param>
        /// <param name="hotelEntity"></param>
        /// <returns></returns>
        internal static int UpdatePayApplyPayStatus(DateTime payTime, PayStatusEnum status,string paymentMethod,string voucher, string remark,string audit,string payApplyId )
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_PayApply SET [payTime]='{0}', [PayStatus]='{1}',[PaymentMethod] ='{2}',[Voucher]='{3}',[Remark]='{4}',[Audit]='{5}' 
                                        WHERE PayApplyID={6}", 
                                        payTime.ToString("yyyy-MM-dd"),status,paymentMethod,voucher,remark,audit,payApplyId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 修改凭证号
        /// </summary>
        /// <param name="voucher"></param>
        /// <param name="payApplyId"></param>
        /// <returns></returns>
        //internal static int UpdatePayApplyVoucher(string voucher, string payApplyId)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string sql = string.Format(@"UPDATE fin_PayApply SET [Voucher]='{0}' WHERE PayApplyID={1}",voucher, payApplyId);

        //    return db.ExecuteNonQuery(CommandType.Text, sql);
        //}
        /// <summary>
        /// 修改凭证号
        /// </summary>
        /// <param name="voucher"></param>
        /// <param name="payApplyId"></param>
        /// <returns></returns>
        internal static int UpdatePayVoucher(string voucher, string payApplyId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_Pay SET [Voucher]='{0}' WHERE PayID={1}", voucher, payApplyId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 根据付款申请单ID，修改财务付款状态及大小写‘汇款总额’
        /// </summary>
        /// <param name="payStatusEnum"></param>
        /// <param name="payApplyId"></param>
        /// <returns></returns>
        internal static int UpdatePayStatusAndApplyPayForPayApply(PayStatusEnum payStatusEnum,decimal totalpaymentL, string payApplyId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_PayApply SET [PayStatus]='{0}',[TotalpaymentL]={1},[TotalpaymentG]='{2}' WHERE PayApplyID={3}",
                                        payStatusEnum,
                                        totalpaymentL,
                                        MoneyHelper.ChMoney(totalpaymentL), 
                                        payApplyId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        //internal static int UpdateRealPaymentForPayApply(string payApplyID, decimal realPayment)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string sql = string.Format(@"UPDATE fin_PayApply SET [RealPayment]={0} WHERE PayApplyID={1}", realPayment, payApplyID);

        //    return db.ExecuteNonQuery(CommandType.Text, sql);
        //}
        /// <summary>
        /// 根据付款ID号删除付款申请单及付款详情数据
        /// </summary>
        /// <param name="payApplyId"></param>
        /// <returns></returns>
        internal static int DeletePayApplyAndPayDetail(string payApplyId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"DELETE FROM fin_PayApply WHERE PayApplyID={0};DELETE FROM fin_PayDetail WHERE PayApplyID={1}", payApplyId, payApplyId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #region 付款单
        /// <summary>
        ///  创建付款单数据记录
        /// </summary>
        /// <param name="payEntity"></param>
        /// <returns>新增的付款单ID</returns>
        internal static string CreatePayData(PayEntity payEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO fin_Pay (    [ProviderID],[ProviderName],[PayAmount], 
                                                                       [Applicants], [PayTime],[ReducedItem],[ReduceAmount],[PaymentMethod],[Voucher],[Remark])
                                                                       VALUES({0},'{1}',{2},'{3}','{4}','{5}',{6},'{7}','{8}','{9}');Select SCOPE_IDENTITY();",
                                                                        payEntity.ProviderID,
                                                                        payEntity.ProviderName,
                                                                        payEntity.PayAmount,
                                                                        payEntity.Applicants,
                                                                        payEntity.PayTime,
                                                                        payEntity.ReducedItem,
                                                                        payEntity.ReduceAmount,
                                                                        payEntity.PaymentMethod,
                                                                        payEntity.Voucher,
                                                                        payEntity.Remark
                                                                        );
            string payId = db.ExecuteScalar(CommandType.Text, sql).ToString();
            return payId;
        }

        /// <summary>
        /// 根据付款申请单更新付款详情的付款单号和支付状态
        /// </summary>
        /// <param name="voucher"></param>
        /// <param name="payApplyId"></param>
        /// <returns></returns>
        internal static int UpdatePayDetailbyPayApplyId(string payId, string payApplyId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_PayDetail SET [PayID]={0} ,[PayStatus] ='{1}',[PayTime]='{2}' WHERE PayApplyID={3}", payId, PayDetailStatusEnum.已付, DateTime.Now.ToString("yyyy-MM-dd"), payApplyId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        internal static int UpdatePayDetailbyPayDetailId(string payId,string payDetialId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_PayDetail SET [PayID]={0} ,[PayStatus] ='{1}',[PayTime]='{2}' WHERE PayDetailID={3}", payId, PayDetailStatusEnum.已付, DateTime.Now.ToString("yyyy-MM-dd"),payDetialId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int UpdatePayApplyStatus(string payDetialId, string voucher)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string payApplyID;
            //string sql = string.Format("select PayApplyID from fin_PayDetail where PayDetailID={0}", payDetialId);
            string sql = string.Format("select PayApplyID from fin_PayDetail where PayDetailID={0}", payDetialId);

            dbCommand = db.GetSqlStringCommand(sql);
            payApplyID = db.ExecuteScalar(dbCommand).ToString();

            sql = string.Format("select count(*) from fin_PayDetail where PayStatus='{0}' AND PayApplyID = {1}", PayDetailStatusEnum.未付, payApplyID);

            dbCommand = db.GetSqlStringCommand(sql);

            object obj = db.ExecuteScalar(dbCommand);
            if (obj.ToString() != "0")
            {
                sql = string.Format(@"UPDATE fin_PayApply SET PayStatus= '{0}',PaymentMethod = '批量付款' WHERE PayApplyID={1}", PayStatusEnum.部分付, payApplyID);
                db.ExecuteNonQuery(CommandType.Text, sql);
            }
            else
            {
                sql = string.Format(@"UPDATE fin_PayApply SET PayStatus= '{0}',PaymentMethod = '批量付款' WHERE PayApplyID={1}", PayStatusEnum.已付, payApplyID);
                db.ExecuteNonQuery(CommandType.Text, sql);
            }

            sql = string.Format(@"UPDATE fin_PayApply SET Voucher = Voucher + '{0} ' WHERE Voucher not like '%{0}%' AND PayApplyID={1}", voucher,payApplyID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion


    }
}
